﻿using Common;
using DBUtility;
using Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BaseDal
{
    public class SupplierDal
    {
        static readonly Lazy<SupplierDal> dal = new Lazy<SupplierDal>(() => new SupplierDal());
        public static SupplierDal Instance { get; } = dal.Value;
        private SupplierDal() { }

        private const string tableName = "Supplier";

        /// <summary>
        /// 查询数据是否存在
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public bool IsExist(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from " + tableName);
            strSql.Append(" where 1=1 " + strWhere);
            return SqlHelper.Exists(strSql.ToString());
        }

        /// <summary>
        /// 查询SQL语句
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public StringBuilder GetSelectSql(string strWhere = "")
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT ID,S_Number,S_Name,S_Address,S_Status,S_Contacts,S_Phone,S_Email,");
            sb.Append("S_Bank,S_BankNumber,S_Equipment ");
            sb.Append("FROM " + tableName);
            sb.Append(" Where 1=1 ");

            if (!string.IsNullOrEmpty(strWhere))
            {
                sb.Append(strWhere);
            }

            return sb;
        }

        /// <summary>
        /// 查询分页数据
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public DataTable GetPageList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder sb = GetSelectSql(strWhere);
            string countSql = PageHelper.CreateCountingSql(sb.ToString());
            recordCount = Convert.ToInt32(SqlHelper.GetSingle(countSql));
            string pageSql = PageHelper.CreatePageSql(recordCount, pageSize, pageIndex, sb.ToString(), filedOrder);
            DataSet ds = SqlHelper.Query(pageSql);
            return ds.Tables[0];
        }

        /// <summary>
        /// 查询数据列表
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public List<Supplier> GetList(string strWhere)
        {
            StringBuilder sb = GetSelectSql(strWhere);
            DataSet ds = SqlHelper.Query(sb.ToString());
            return CommonOperation.ConvertDataTableToModelList<Supplier>(ds.Tables[0]);
        }

        /// <summary>
        /// 根据id查询实体类
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Supplier GetModelById(int id)
        {
            StringBuilder sb = GetSelectSql("and ID=@id");
            SqlParameter[] parameters =
            {
                new SqlParameter("@id",id)
            };
            DataSet ds = SqlHelper.Query(sb.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return CommonOperation.DataTableToModel<Supplier>(ds.Tables[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 新增供应商
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool AddSupplier(Supplier model)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("INSERT INTO " + tableName + "(");
            sb.Append("S_Number,S_Name,S_Address,S_Contacts,S_Phone,S_Email,S_Bank,S_BankNumber,S_Equipment)");
            sb.Append(" VALUES(");
            sb.Append("@number,@name,@address,@contacts,@phone,@email,@bank,@bankNumber,@equipment)");
            SqlParameter[] parameters =
           {
                new SqlParameter("@number",model.S_Number),
                new SqlParameter("@name",model.S_Name),
                new SqlParameter("@address",model.S_Address),
                new SqlParameter("@contacts",model.S_Contacts),
                new SqlParameter("@phone",model.S_Phone),
                new SqlParameter("@email",model.S_Email),
                new SqlParameter("@bank",model.S_Bank),
                new SqlParameter("@bankNumber",model.S_BankNumber),
                new SqlParameter("@equipment",model.S_Equipment)
            };
            int result = SqlHelper.ExecuteSql(sb.ToString(), parameters);
            return result > 0;
        }

        /// <summary>
        /// 修改供应商
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool UpdateSupplier(Supplier model)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("Update " + tableName + " SET ");
            sb.Append("S_Number=@number,");
            sb.Append("S_Name=@name,");
            sb.Append("S_Address=@address,");
            sb.Append("S_Contacts=@contacts,");
            sb.Append("S_Phone=@phone,");
            sb.Append("S_Email=@email,");
            sb.Append("S_Bank=@bank,");
            sb.Append("S_BankNumber=@bankNumber,");
            sb.Append("S_Equipment=@equipment ");
            sb.Append("WHERE ID=@id");
            SqlParameter[] parameters = {
                new SqlParameter("@number",model.S_Number),
                new SqlParameter("@name",model.S_Name),
                new SqlParameter("@address",model.S_Address),
                new SqlParameter("@contacts",model.S_Contacts),
                new SqlParameter("@phone",model.S_Phone),
                new SqlParameter("@email",model.S_Email),
                new SqlParameter("@bank",model.S_Bank),
                new SqlParameter("@bankNumber",model.S_BankNumber),
                new SqlParameter("@equipment",model.S_Equipment),
                new SqlParameter("@id",model.ID)
            };
            int result = SqlHelper.ExecuteSql(sb.ToString(), parameters);
            return result > 0;
        }

        /// <summary>
        /// 删除供应商
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public bool DeleteSupplier(string ids)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append(" Delete from " + tableName);
            strSql.Append(" Where ID in(" + ids + ")");
            int result = SqlHelper.ExecuteSql(strSql.ToString());
            return result > 0;
        }

    }
}
